package com.apes.fn.scm.advancePayment;

import com.apes.framework.jpa.repository.CustomRepository;
import org.springframework.data.domain.Page;
import org.springframework.data.domain.Pageable;
import org.springframework.data.jpa.repository.Query;

import java.util.Date;
import java.util.Map;

/**
 * @author
 * @create 2018-10-31 16:07
 */
public interface AdvancePaymentRepository extends CustomRepository<AdvancePayment, String> {
    @Query(value = " SELECT  B.PRICE_TOTAL AS PRICETOTAL, 0 DISCOUNTAMOUNT,  " +
            "       (select name from mas_dept where id = a.dept_id) as deptName,  " +
            "       (select '[' || id || ']' || NAME from MAS_PRODUCT where ID = b.PRODUCT_ID) productName,  " +
            "       (SELECT C.NAME FROM MAS_PARTY_ROLE B, MAS_PARTY C WHERE a.creator_id = B.ID AND B.PARTY_ID = C.ID) customer,  " +
            "       A.ID  " +
            "      FROM ADVANCE_PAYMENT A inner join ADVANCE_PAYMENT_item b on A.ID = b.ADVANCE_PAYMENT_ID  " +
            "     WHERE A.State = 'approve'" +
            "AND GET_DATE(CAST(a.CREATE_DATE AS DATE)) <= ?3" +
            " AND GET_DATE(CAST(a.CREATE_DATE AS DATE)) >= ?2" +
            " AND A.COMPANY_ID = ?1 ",
            countQuery = "SELECT count(*)  " +
                    "      FROM ADVANCE_PAYMENT A inner join ADVANCE_PAYMENT_item b on A.ID = b.ADVANCE_PAYMENT_ID  " +
                    "     WHERE A.State = 'approve'" +
                    "AND GET_DATE(CAST(a.CREATE_DATE AS DATE)) <= ?3" +
                    " AND GET_DATE(CAST(a.CREATE_DATE AS DATE)) >= ?2 " +
                    " AND A.COMPANY_ID = ?1", nativeQuery = true
    )
    Page<Map> findByCompanyIdAndServeTypeAndCreateDate(String companyId, Date startDate, Date endDate, Pageable pageable);

    AdvancePayment findByOrigin(String origin);
}
